import pymysql

class DBHelper:
    # 方法
    # 连接数据库
    def openDB(self):
        global conn
        conn = pymysql.connect(host='127.0.0.1', user='root', password='mysql123456', db='test', charset='utf8')
        global evo
        evo = conn.cursor()

    # 关闭数据库
    def closeDB(self):
        evo.close()
        conn.close()

    # 数据增删改
    def edit(self, sql):
        result = 0
        self.openDB()
        try:
            result = evo.execute(sql)
            conn.commit()
        except pymysql.err.IntegrityError:
            result = -1
            conn.rollback()
            return result
        except pymysql.err.DataError:
            result = -2
            conn.rollback()
            return result
        finally:
            self.closeDB()
        return result



    
    # 查询一条
    def selectOne(self, sql):
        self.openDB()
        evo.execute(sql)
        result = evo.fetchone()
        self.closeDB()
        return result

    # 查询多条
    def selectAll(self, sql):
        self.openDB()
        evo.execute(sql)
        result = evo.fetchall()
        self.closeDB()
        return result


# 车辆信息表    CarInfo
class CarInfoDAL(DBHelper):
    # 根据类型查找车辆信息
    def queryByCarType(self, carType):
        sql = f"select * from CarInfo where carType = '{carType}'"
        return self.selectAll(sql)
    
    # 根据车牌号查找车辆信息
    def queryByCarNumber(self, carNumber):
        sql = f"select * from CarInfo where carNumber = '{carNumber}'"
        return self.selectOne(sql)

    # 根据车牌号修改车辆信息
    def updateByCarNumber(self, carInfo):
        sql = f"""update CarInfo set carType = '{carInfo.carType}',
        carStatus = '{carInfo.carStatus}', carYear = '{carInfo.carYear}',carInsuranceDate = '{carInfo.carInsuranceDate}', 
        carCheckDate = '{carInfo.carCheckDate}', carPrice = {carInfo.carPrice}, 
        carHeader = '{carInfo.carHeader}', carHeaderPhone = '{carInfo.carHeaderPhone}' 
        where carNumber = '{carInfo.carNumber}'"""
        return self.edit(sql)
    
    # 根据车牌号获取车辆类型
    def queryCarTypeByCarNumber(self, carNumber):
        sql = f"select carType from CarInfo where carNumber = '{carNumber}'"
        return self.selectOne(sql)

    # 根据车牌号删除车辆信息
    def deleteByCarNumber(self, carNumber):
        sql = f"delete from CarInfo where carNumber = '{carNumber}'"
        return self.edit(sql)
    
    # 新增车辆信息
    def insert(self, carInfo):
        sql = f"""insert into CarInfo values('{carInfo.carNumber}', '{carInfo.carType}', '{carInfo.carStatus}', 
        {carInfo.carYear}, '{carInfo.carInsuranceDate}', '{carInfo.carCheckDate}', {carInfo.carPrice}, 
        '{carInfo.carHeader}', '{carInfo.carHeaderPhone}')"""
        return self.edit(sql)
    
    # 查询车辆总数量
    def queryCarAmount(self):
        sql = 'select * from CarInfo'
        return self.edit(sql)
    
    # 查询未使用车辆数量
    def queryCarStatusIsNotUseAmount(self):
        sql = "select count(1) from CarInfo where carStatus = '未使用'"
        return self.selectOne(sql)
    
    # 查询在使用车辆数量
    def queryCarStatusIsUseAmount(self):
        sql = "select count(1) from CarInfo where carStatus = '在使用'"
        return self.selectOne(sql)
    

# 车辆库存信息表    CarStockInfo
class CarStockInfoDAL(DBHelper):
    # 查找车辆所有库存信息
    def queryAll(self):
        sql = f"select * from CarStockInfo"
        return self.selectAll(sql)
    
    # 根据车辆类型查找车辆库存信息
    def queryByCarType(self, carType):
        sql = f"select * from CarStockInfo where carType = '{carType}'"
        return self.selectOne(sql)

    # 根据车辆类型修改库存信息
    def updateByCarType(self, carStockInfo):
        sql = f"""update CarStockInfo set carAmount = {carStockInfo.carAmount}, 
            carTypeHeader = '{carStockInfo.carTypeHeader}', 
            carTypeHeaderPhone = '{carStockInfo.carTypeHeaderPhone}' 
            where carType = '{carStockInfo.carType}'"""
        return self.edit(sql)
    
    # 新增车辆类型库存信息
    def insert(self, carStockInfo):
        sql = f"""insert into CarStockInfo values('{carStockInfo.carType}', {carStockInfo.carAmount}, 
        '{carStockInfo.carTypeHeader}', '{carStockInfo.carTypeHeaderPhone}')"""
        return self.edit(sql)
    
    # 删除车辆类型库存信息
    def deleteByCarType(self, carType):
        sql = f"delete from CarStockInfo where carType = '{carType}'"
        return self.edit(sql)
    
    # 车辆类型库存数量减少
    def carAmountLostOne(self, carType):
        sql = f"update CarStockInfo set carAmount = carAmount - 1 where carType = '{carType}'"
        return self.edit(sql)
    
    # 车辆类型库存数量增加
    def carAmountAddOne(self, carType):
        sql = f"update CarStockInfo set carAmount = carAmount + 1 where carType = '{carType}'"
        return self.edit(sql)
    
    # 查询车辆类型和车辆库存数量信息
    def queryCarTypeAndCarAmount(self):
        sql = 'select carType, carAmount  from CarStockInfo'
        return self.selectAll(sql)
    
    
    

# 车辆使用信息表    carUseInfo
class CarUseInfoDAL(DBHelper):
    # 查询所有车辆使用信息
    def queryAll(self):
        sql = "select * from CarUseInfo"
        return self.selectAll(sql)
    
    # 根据记录号查找车辆使用信息
    def queryByCarUseId(self, carUseId):
        sql = f"select * from CarUseInfo where carUseId = '{carUseId}'"
        return self.selectOne(sql)
    
    # 根据记录号修改车辆使用信息
    def updateByCarUseId(self, carUseInfo):
        sql = f"""update CarUseInfo set carNumber = '{carUseInfo.carNumber}', useMotive = '{carUseInfo.useMotive}', 
        useDate = '{carUseInfo.useDate}', useTime = {carUseInfo.useTime}, workerName = '{carUseInfo.workerName}', 
        workerPhone = '{carUseInfo.workerPhone}', carHeader = '{carUseInfo.carHeader}', carHeaderPhone = '{carUseInfo.carHeaderPhone}', 
        carTypeHeader = '{carUseInfo.carTypeHeader}', carTypeHeaderPhone = '{carUseInfo.carTypeHeaderPhone}' 
        where carUseId = {carUseInfo.carUseId}"""
        return self.edit(sql)
    
    # 根据记录号删除车辆使用信息
    def deleteByCarUseId(self, carUseId):
        sql = f"delete from CarUseInfo where carUseId = {carUseId}"
        return self.edit(sql)
    
    # 新增车辆使用信息
    def insert(self, carUseInfo):
        sql = f"""insert into CarUseInfo values('{carUseInfo.carUseId}', '{carUseInfo.carNumber}', '{carUseInfo.useMotive}', '{carUseInfo.useDate}', 
        {carUseInfo.useTime}, '{carUseInfo.workerName}', '{carUseInfo.workerPhone}', '{carUseInfo.carHeader}', 
        '{carUseInfo.carHeaderPhone}', '{carUseInfo.carTypeHeader}', '{carUseInfo.carTypeHeaderPhone}')"""
        return self.edit(sql)


# 工作人员信息表    Worker
class WorkerDAL(DBHelper):
    # 查询所有工作人员信息
    def queryAll(self):
        sql = "select * from Worker" 
        return self.selectAll(sql)
    
    # 根据姓名查找工作人员信息
    def queryByWorkerName(self, workerName):
        sql = f"select * from Worker where workerName = '{workerName}'"
        return self.selectOne(sql)
    
    # 根据姓名修改工作人员信息
    def updateByWorkerName(self, worker):
        sql = f"""update Worker set workerAge = {worker.workerAge}, 
        workerType = '{worker.workerType}', workerDriver='{worker.workerDriver}', 
        workerDriverYear = '{worker.workerDriverYear}', workerPhone = '{worker.workerPhone}' 
        where workerName = '{worker.workerName}'"""
        return self.edit(sql)
    
    # 根据姓名删除工作人员信息
    def deleteByWorkerName(self, workerName):
        sql = f"delete from Worker where workerName = '{workerName}'"
        return self.edit(sql)
    
    # 新增工作人员信息
    def insert(self, worker):
        sql = f"""insert into Worker values('{worker.workerName}', {worker.workerAge}, '{worker.workerType}', 
        '{worker.workerDriver}', '{worker.workerDriverYear}', '{worker.workerPhone}')"""
        return self.edit(sql)
    
    # 查询工作人员类型数量信息
    def queryWorkerTypeCount(self):
        sql = 'select workerType, count(1) from Worker group by workerType'
        return self.selectAll(sql)


# 工作人员类型表    WorkerType
class WorkerTypeDAL(DBHelper):
    # 查询所有工作人员类型信息
    def queryAll(self):
        sql = "select * from WorkerType"
        return self.selectAll(sql)


# 车辆类型信息表    CarType
class CarTypeDAL(DBHelper):
    # 查询所有车辆类型信息
    def queryAll(self):
        sql = "select * from CarType"
        return self.selectAll(sql)

    # 添加车辆类型
    def insert(self,carType):
        sql = f"insert into CarType values('{carType}')"
        return self.edit(sql)


# 车辆状态信息表    CarStatus
class CarStatusDAL(DBHelper):
    # 查询所有车辆状态信息
    def queryAll(self):
        sql = "select * from CarStatus"
        return self.selectAll(sql)
    



# 管理员密码
class AdminPassWordDAL(DBHelper):
    # 查询所有管理员密码
    def queryPassWord(self):
        sql = "select * from AdminPassWord"
        return self.selectOne(sql)
    
    # 修改管理员密码
    def updatePassWord(self,oldAdminPassWord,  newAdminPassWord):
        sql = f"update AdminPassWord set adminPassWord = '{newAdminPassWord}'"
        return self.edit(sql)